package com.util;

import com.bean.jBean;
import com.bean.orderAllBean;
import com.bean.usersBean;
import com.bean.lineItem;
import com.bean.numberBean;
import com.bean.orderBean;
import com.bean.commodityBean;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MyUtil {
    private static Connection source = null;
    public static Connection utils(){
        if(source==null){
            try {
                Class.forName("com.mysql.jdbc.Driver");
                String url = "jdbc:mysql://localhost:3306/huagong?characterEncoding=UTF-8";
                String user = "root";
                String password = "123456";
                source = DriverManager.getConnection(url,user,password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return source;
    }

    /**
     * 查询所有 分页
     * @return
     * @throws SQLException
     */
    public static List<orderAllBean> selectAll(Integer a,String name,String userId,String status) throws SQLException {
        QueryRunner qr =new QueryRunner();
        String sql = "";
        ArrayList arr = new ArrayList();
        if(("0".equals(status))){//管理员可以看到全部订单
            sql = "select a.did did,b.`name` name,a.amount amount,a.price price,(a.price * a.amount) totalPrice,a.cost_price cost_price,(a.cost_price * a.price) totalCostPrice,a.royalties royalties,a.create_time createTime,a.other_cost otherCost,a.bill bill,a.status status,a.invoice_id number,b.user_id user_id,a.user_id user_id2,a.user_name user_name,a.phone phone  from `order` a,commodity b where a.commodity_id=b.sid ";
            if(!"".equals(name)){
                sql+=" and name like ?";
                arr.add("%"+name+"%");
            }
            if(!"".equals(a)){
                sql+=" limit ?,10";
                arr.add(a);
            }
        }if("1".equals(status)){//业务员只能看到自己的订单 或者是自己采购的商品
            sql = "select a.did did,b.`name` name,a.amount amount,a.price price,(a.price * a.amount) totalPrice,a.cost_price cost_price,(a.cost_price * a.price) totalCostPrice,a.royalties royalties,a.create_time createTime,a.other_cost otherCost,a.bill bill,a.status status,a.invoice_id number,b.user_id user_id,a.user_id user_id2,a.user_name user_name,a.phone phone  from `order` a,commodity b where a.commodity_id=b.sid ";
            if(!"".equals(name)){
                sql+=" and name like ?";
                arr.add("%"+name+"%");
            }
            if(!"".equals(userId)){
                sql+=" and  (b.user_id=? or a.user_id=?) ";
                arr.add(userId);
                arr.add(userId);
            }
            if(!"".equals(a)){
                sql+=" limit ?,10";
                arr.add(a);
            }
        }
        List<orderAllBean> list = qr.query(MyUtil.utils(),sql,new BeanListHandler<orderAllBean>(orderAllBean.class),arr.toArray());
        for(orderAllBean order:list){
            String string = String.format("YD%05d",order.getDid());
            order.setOrderId(string);
        }
        return list;
    }

    /**
     * 获取分页页数
     */
    public static jBean selectLimit( String name,String begin,String finish) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select count(*) line from `order` a,commodity b where a.commodity_id=b.sid and 1=1 ";
        ArrayList arr = new ArrayList();
        if(!"".equals(begin) && !"".equals(finish)){
            sql+=" and a.create_time>=? and a.create_time<=?";
            arr.add(begin);
            arr.add(finish);
        }
        if(!"".equals(name)){
            sql+=" and b.name like ? ";
            arr.add("%"+name+"%");
        }
        jBean jbean = qr.query(salesUtil.utils(),sql,new BeanHandler<jBean>(jBean.class),arr.toArray());
        return jbean;
    }

    /**
     * 登录验证
     * @param user
     * @param admin
     * @return
     * @throws SQLException
     */
    public static usersBean selectUser(String user,String admin) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select * from users where user_name=? and password=?";
        usersBean usersBean = qr.query(MyUtil.utils(),sql,new BeanHandler<usersBean>(usersBean.class),user,admin);
        return usersBean;
    }

    /**
     * 根据条件模糊查询数据
     * @param name
     * @param begin
     * @param finish
     * @return
     * @throws SQLException
     */
    public static List<orderAllBean> selectOrder(String name,String begin,String finish) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select a.did did,b.sid orderId,b.`name` name,a.amount amount,a.price price,(a.price * a.amount) totalPrice,a.cost_price cost_price,(a.cost_price * a.price) totalCostPrice,a.royalties royalties,a.create_time createTime,a.other_cost otherCost,a.bill bill,a.status status,b.user_id user_id,a.user_id user_id2  from `order` a,commodity b where a.commodity_id=b.sid and 1=1 ";
        ArrayList arr = new ArrayList();

        if(!"".equals(begin) && !"".equals(finish)){
            sql+=" and a.create_time>=? and a.create_time<=?";
            arr.add(begin);
            arr.add(finish);
        }
        if(!"".equals(name)){
            sql+=" and name like ? ";
            arr.add("%"+name+"%");
        }if("1"=="1"){
            sql+=" limit 0,10";
        }
        List<orderAllBean> list = qr.query(MyUtil.utils(),sql,new BeanListHandler<orderAllBean>(orderAllBean.class),arr.toArray());
        for(orderAllBean order:list){
            String string = String.format("YD%05d",order.getDid());
            order.setOrderId(string);
        }
        return list;
    }
    public static lineItem selectOrders(String sid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select b.sid orderId,b.`name` name,a.amount amount,a.price price,(a.price * a.amount) totalPrice,a.cost_price cost_price,(a.cost_price * a.price) totalCostPrice,a.royalties royalties,a.create_time createTime,a.other_cost otherCost,a.bill bill,\n" +
                " a.user_name userName,a.address address ,a.phone phone,a.bill_info billInfo,a.invoice_id invoiceId,a.remarks remarks \n" +
                "from `order` a,commodity b\n" +
                "where a.commodity_id=b.sid and  a.did=?";
        lineItem line = qr.query(MyUtil.utils(),sql,new BeanHandler<lineItem>(lineItem.class),sid);
         return line;
    }
    public static List<jBean> selectOrderName(String cas, String name) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select a.sid,a.name,b.number from commodity a,inventory b where  a.cas=b.cas ";
        ArrayList arr = new ArrayList();
        if(!"".equals(cas)){
            sql+=" and a.cas=?";
            arr.add(cas);
        }if(!"".equals(name)){
            sql+=" and a.name like ?";
            arr.add("%"+name+"%");
        }
        List<jBean> list = qr.query(MyUtil.utils(),sql,new BeanListHandler<jBean>(jBean.class),arr.toArray());
       return list;
    }
    public static jBean select(String cas,String name) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select a.sid sid,a.name name,b.number number from commodity a,inventory b where  a.cas=? and a.name=?";
        jBean j = null;
        try {
            j = qr.query(MyUtil.utils(),sql,new BeanHandler<jBean>(jBean.class),cas,name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return j;
    }
    public static void insert(Object...params) throws SQLException {
         QueryRunner qr = new QueryRunner();
        String sql = "INSERT into `order`(user_id,amount,price,cost_price,royalties,other_cost,bill,bill_info,commodity_id,invoice_id,`status`,user_name,address,phone,create_time,remarks) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        int number = qr.update(MyUtil.utils(),sql,params);
    }
    public static List<numberBean> selectNumber(String sid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select b.number from commodity a,inventory b where a.cas=b.cas and a.sid=? ";
        List<numberBean> list = qr.query(MyUtil.utils(),sql,new BeanListHandler<numberBean>(numberBean.class),sid) ;
        return list;
    }
    public static List<commodityBean> select1() throws SQLException {//查询全部商品名
        QueryRunner qr = new QueryRunner();
        String sql = "select sid,name from commodity";
        List<commodityBean> list = qr.query(MyUtil.utils(),sql,new BeanListHandler<commodityBean>(commodityBean.class)) ;
       return list;
    }

    /**
     * 删除一条订单
     * @param did  订单编号
     * @throws SQLException
     */
    public static void delete1(String did) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "delete from `order` where did=?";
        int number =qr.update(MyUtil.utils(),sql,did);
    }

    /**
     * 查询业务员身份
     * @param userId
     */
    public static usersBean selectUser(String userId) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT type from users where uid=?";
        usersBean users = qr.query(MyUtil.utils(),sql,new BeanHandler<usersBean>(usersBean.class),userId);
        return users;
    }
    public static lineItem select3(String did) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select b.sid orderId,b.`name` name,a.amount amount,a.price price,(a.price * a.amount) totalPrice,a.cost_price cost_price,(a.cost_price * a.price) totalCostPrice,a.royalties royalties,a.create_time createTime,a.other_cost otherCost,a.bill bill,\n" +
                " a.user_name userName,a.address address ,a.phone phone,a.bill_info billInfo,a.invoice_id invoiceId,a.remarks remarks \n" +
                "from `order` a,commodity b\n" +
                "where  a.commodity_id=b.sid and a.did=?";
        lineItem lineitem = qr.query(MyUtil.utils(),sql,new BeanHandler<lineItem>(lineItem.class),did);
        return lineitem;
    }
    public static void update(Object...objects) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE `order` set amount=?,price=?,cost_price=?,royalties=?,other_cost=?,bill=?,bill_info=?,commodity_id=?,invoice_id=?,user_name=?,address=?,phone=?,remarks=? where did=? ";
        qr.update(MyUtil.utils(),sql,objects);
    }

    /**
     * 计算行数
     * @return
     * @throws SQLException
     */
    public static numberBean selectCount() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select count(*) number from `order`";
        numberBean number = qr.query(purchaseUtil.utils(),sql,new BeanHandler<numberBean>(numberBean.class));
        return number;
    }

    /**
     * 修改出库状态
     *
     * @param id
     * @throws SQLException
     */
    public static void update(String id) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE `order` set `status`='1' where did=? ";
        qr.update(salesUtil.utils(),sql,id);

    }
    /**
     * 点击已出库 修改库存量
     */
    public static void update(String amount,String number) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE inventory set amount=amount-? where number =?";
        qr.update(salesUtil.utils(),sql,amount,number);
    }

}
